package dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import exception.DAOException;

import bean.Book;

public class BookManagementDAO {

	private Connection connection = null;
	private CallableStatement callableStatement = null;
	private ResultSet resultSet = null;

	public ArrayList<Book> getAllBook(String keyword, String searchBy,
			int currentPage) throws DAOException {
		ArrayList<Book> bookList = new ArrayList<Book>();
		try {
			connection = BaseDAO.getConnection();
			String sql = "{call book_listBook_getbookByPagination(?,?,?,?)}";
			callableStatement = connection.prepareCall(sql);
			callableStatement.setString(1, keyword);
			callableStatement.setString(2, searchBy);
			callableStatement.setInt(3, currentPage);
			callableStatement.setInt(4, 20);
			resultSet = callableStatement.executeQuery();

			Book book = null;

			if (resultSet != null) { // Check whether Result is null or not
				while (resultSet.next()) { // Process of each record
					book = new Book();
					book.setBookID(resultSet.getInt("BookID"));
					book.setBookName(resultSet.getString("BookName"));
					book.setAuthor(resultSet.getString("Author"));
					book.setCategoryID(resultSet.getInt("CategoryID"));
					book.setPublisher(resultSet.getString("Publisher"));
					book.setPrice(resultSet.getFloat("Price"));
					book.setDescription(resultSet.getString("Description"));
					book.setPhoto(resultSet.getString("Photo"));
					book.setUnitsInStock(resultSet.getInt("UnitsInStock"));
					bookList.add(book);
				}
			}
		} catch (SQLException e) {
			throw new DAOException(e.getMessage());
		} catch (DAOException e) {
			throw new DAOException(e.getMessage());
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (callableStatement != null)
					callableStatement.close();
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}
		}
		return bookList;
	}

	public int getTotalBookRow(String keyword, String searchBy)
			throws DAOException {
		int totalRow = 0;
		try {
			connection = BaseDAO.getConnection();
			String sql = "{call book_listBook_getTotalBookRow(?,?)}";
			callableStatement = connection.prepareCall(sql);
			callableStatement.setString(1, keyword);
			callableStatement.setString(2, searchBy);
			resultSet = callableStatement.executeQuery();

			if (resultSet != null) { // Check whether Result is null or not
				while (resultSet.next()) { // Process of the result
					totalRow = resultSet.getInt(1);
				}
			}
		} catch (SQLException e) {
			throw new DAOException(e.getMessage());
		} catch (DAOException e) {
			throw new DAOException(e.getMessage());
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (callableStatement != null)
					callableStatement.close();
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}
		}
		return totalRow;
	}

	public ArrayList<Book> getAllBook(int currentPage) throws DAOException {
		ArrayList<Book> bookList = new ArrayList<Book>();
		try {
			connection = BaseDAO.getConnection();
			String sql = "{call book_listBook_getbookByPagination(?,?)}";
			callableStatement = connection.prepareCall(sql);
			callableStatement.setInt(1, currentPage);
			callableStatement.setInt(2, 20);
			resultSet = callableStatement.executeQuery();
			Book book = null;

			if (resultSet != null) { // Check whether Result is null or not
				while (resultSet.next()) { // Process of each record
					book = new Book();
					book.setBookID(resultSet.getInt("BookID"));
					book.setBookName(resultSet.getString("BookName"));
					book.setAuthor(resultSet.getString("Author"));
					book.setCategoryID(resultSet.getInt("CategoryID"));
					book.setPublisher(resultSet.getString("Publisher"));
					book.setPrice(resultSet.getFloat("Price"));
					book.setDescription(resultSet.getString("Description"));
					book.setPhoto(resultSet.getString("Photo"));
					book.setUnitsInStock(resultSet.getInt("UnitsInStock"));
					bookList.add(book);
				}
			}
		} catch (SQLException e) {
			throw new DAOException(e.getMessage());
		} catch (DAOException e) {
			throw new DAOException(e.getMessage());
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (callableStatement != null)
					callableStatement.close();
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}
		}
		return bookList;
	}

	public int getTotalBookRow() throws DAOException {
		int totalRow = 0;
		try {
			connection = BaseDAO.getConnection();
			String sql = "{call book_listBook_getTotalBookRow()}";
			callableStatement = connection.prepareCall(sql);
			resultSet = callableStatement.executeQuery();

			if (resultSet != null) { // Check whether Result is null or not
				while (resultSet.next()) { // Process of the result
					totalRow = resultSet.getInt(1);
				}
			}
		} catch (SQLException e) {
			throw new DAOException(e.getMessage());
		} catch (DAOException e) {
			throw new DAOException(e.getMessage());
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
				if (callableStatement != null)
					callableStatement.close();
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				throw new DAOException(e.getMessage());
			}
		}
		return totalRow;
	}
}